/*
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 *
 * @authors Cavenaghi, Collado, De La Cruz and Bosio
 */
package conexion;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Locale;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.sql.DataSource;

public class SqlManager implements Serializable {

    private static final long serialVersionUID = -5234473242999323611L;
    public static final int SINGLECONNECTIONMODE = 1;
    public static final int POOLCONNECTIONMODE = 2;
    private int connectionMode = SINGLECONNECTIONMODE;
    /*
    private String driverName = "org.postgresql.Driver";
    private String url = "jdbc:postgresql://localhost:5432/cementera3";
    private String resourceName = "java:comp/env/jdbc/gestion";
    private String usr = "postgres";
    private String pwd = "282146facu";
     */
//    private String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//    private String url = "jdbc:sqlserver://localhost:1433;databaseName=PampaChin
    private String driverName = "oracle.jdbc.driver.OracleDriver";
    private String url = "jdbc:oracle:thin:@localhost:1521:XE";
    //private String resourceName = "java:comp/env/jdbc/gestion";
    private String usr = "hr";
    private String pwd = "Lunes10";
    Context ctx = null;
    DataSource ds = null;
    Connection cn = null;
    Statement stmt = null;
    PreparedStatement ps = null;
    CallableStatement cs = null;

    /**
     * Creates a new instance of SqlManager
     */
    public SqlManager() {
        super();
    }

//    private void setContext() throws Exception {
//        if (this.resourceName == null) {
//            throw new Exception("SQLManager ERROR: ResourceName (JNDI) NO especificado");
//        }
//        this.ctx = new InitialContext();
//        this.ds = (DataSource) this.ctx.lookup(resourceName);
//    }
    public void connect() throws Exception {
        Locale.setDefault(Locale.ENGLISH);
        if (this.connectionMode == SINGLECONNECTIONMODE) {
            Class.forName(driverName);
            this.cn = DriverManager.getConnection(this.url, this.usr, this.pwd);
        } else {
//            this.setContext();
            this.cn = this.ds.getConnection();
        }
        this.stmt = this.cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//        this.ps = this.cn.prepareStatement("");
    }

    public Connection getConnection() throws Exception {
        if (this.cn == null) {
            this.connect();
        }
        return this.cn;
    }

    public PreparedStatement getPs(String query) {
        try {
            return cn.prepareStatement(query);
        } catch (SQLException ex) {
            Logger.getLogger(SqlManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public PreparedStatement getPs() {
        return ps;
    }

    public ResultSet executeQuery(String query) throws Exception {
        return this.stmt.executeQuery(query);
    }

    public ResultSet executeQueryPrepared(PreparedStatement pr) throws Exception {
        ps = pr;
        return ps.executeQuery();
    }

    public int executeUpdate(String statement) throws Exception {
        return this.stmt.executeUpdate(statement);
    }

    public boolean execute() throws Exception {
        return this.ps.execute();
    }

    public void prepare(String statement) throws Exception {
        this.ps = this.cn.prepareStatement(statement, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    }

    public void setInt(int parameterIndex, int value) throws Exception {
        this.ps.setInt(parameterIndex, value);
    }

    public void setShort(int parameterIndex, short value) throws Exception {
        this.ps.setShort(parameterIndex, value);
    }

    public void setShort(int parameterIndex, int value) throws Exception {
        short v = ((Integer) value).shortValue();
        this.setShort(parameterIndex, v);
    }

    public void setSmallInt(int parameterIndex, short value) throws Exception {
        this.setShort(parameterIndex, value);
    }

    public void setSmallInt(int parameterIndex, int value) throws Exception {
        this.setShort(parameterIndex, value);
    }

    public void setString(int parameterIndex, String value) throws Exception {
        this.ps.setString(parameterIndex, value);
    }

    public void setString(int parameterIndex, char value) throws Exception {
        String s = String.valueOf(value);
        this.ps.setString(parameterIndex, s);
    }

    public void setDate(int parameterIndex, Date value) throws Exception {
        this.ps.setDate(parameterIndex, (java.sql.Date) value);
    }

    public void close() throws Exception {
        if (this.cs != null) {
            this.cs.close();
        }
        if (this.stmt != null) {
            this.stmt.close();
        }
        if (this.cn != null) {
            this.cn.close();
        }
        if (this.ctx != null) {
            this.ctx.close();
        }
    }

    public void setCn(Connection cn) {
        this.cn = cn;
    }

    public void setCs(CallableStatement cs) {
        this.cs = cs;
    }

    public void setCtx(Context ctx) {
        this.ctx = ctx;
    }

    public void setDs(DataSource ds) {
        this.ds = ds;
    }

    public void setPs(PreparedStatement ps) {
        this.ps = ps;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public void setStmt(Statement stmt) {
        this.stmt = stmt;
    }

    public void setUsr(String usr) {
        this.usr = usr;
    }

    public static int getPOOLCONNECTIONMODE() {
        return POOLCONNECTIONMODE;
    }

    public static int getSINGLECONNECTIONMODE() {
        return SINGLECONNECTIONMODE;
    }

    public Connection getCn() {
        return cn;
    }

    public CallableStatement getCs() {
        return cs;
    }

    public Context getCtx() {
        return ctx;
    }

    public DataSource getDs() {
        return ds;
    }

    public String getPwd() {
        return pwd;
    }

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public Statement getStmt() {
        return stmt;
    }

    public String getUsr() {
        return usr;
    }

    public int getConnectionMode() {
        return this.connectionMode;
    }

    public void setConnectionMode(int connectionMode) {
        this.connectionMode = connectionMode;
        if (this.connectionMode != POOLCONNECTIONMODE) {
            this.connectionMode = SINGLECONNECTIONMODE;
        }
    }

    public String getDriverName() {
        return this.driverName;
    }

    public void setDriverName(String driverName) {
        this.driverName = driverName;
    }

    public String getUrl() {
        return this.url;
    }

    public void setUrl(String url) {
        this.url = url;
    }
    /*
    public String getResourceName() {
    return this.resourceName;
    }

    public void setResourceName(String resourceName) {
    this.resourceName = resourceName;
    }
     *
     */

    public String getUserName() {
        return usr;
    }

    public void setUserName(String usr) {
        this.usr = usr;
    }

    public String getPassword() {
        return null;
    }

    public void setPassword(String pwd) {
        this.pwd = pwd;
    }
}
